use strict;

require 'dumpvar.pl';

our ($site_ID);
our ($hostname, $database, $username, $password, $port);
my ($new_file_sth, $up_file_sth, $del_file_sth, $del_dir_sth);
my ($get_file_sth, $site_ID_sth, $get_dir_sth, $get_site_from_id_sth, $dbh);
my ($set_unavailable_file_sth, $get_unavailable_file_sth);
our (%new_item_hash);
our (%dir_files);
our ($cur_timestamp, $new_stamp);

our $old_dir_path;	
our $cur_dir_path;

my %dir_id_cache = ();
my $cache_site_id = 0;
our ($filecount, $rescount) = (0,0);


#open database connection
#and initialize those sql statements that may be used in future
sub open_db{
  #set up connection to database
	my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
	$dbh = DBI->connect($dsn, $username, $password, {RaiseError=>1, AutoCommit=>0}) 
		or die $DBI::errstr;
	$dbh->do("SET NAMES 'utf8'");		# IMPORTANT
	
	#sql statment : get all directories from vf_site_directories
	my $get_dir_sql = q{select * FROM vf_site_directories};
	
	#sql statement: insert new file into table vf_files.
	my $new_item_sql = q{INSERT INTO vf_files
		(file_name,
	   file_url, 
		 file_size,
		 file_modify_time,
		 file_is_dir,
		 file_site,
		 file_parent,
		 file_state)
	  VALUES(?, ?, ?, ?, ?, ?, ?, 1)};  #file_state=1 indexNew
	  
	#sql statement: update an item in vf_files
	#file_state=1 indexNew
	my $up_item_sql = q{UPDATE vf_files SET file_size=?, file_modify_time=?,file_state=1 WHERE
		file_name=? AND file_url = ?};
		
	#sql statement: set file_available when file has been deleted on the site
	#file_state=2 indexDel
	my $del_item_sql = q{update vf_files set file_available = 0,file_state=2 WHERE file_name=? AND file_url = ?};
	
	#sql statement: get file_id's of a specified site
	my $get_file_sql = q{SELECT file_id FROM vf_files WHERE file_url  LIKE ? and file_site=?};

  #sql statement: get site_id according to specified site_dns_name
	my $site_ID_sql = q{SELECT site_id FROM vf_sites WHERE site_dns_name=?};

	$new_file_sth=$dbh->prepare($new_item_sql);
	$up_file_sth=$dbh->prepare($up_item_sql);
	$del_file_sth=$dbh->prepare($del_item_sql);
	$get_file_sth=$dbh->prepare($get_file_sql);
	$site_ID_sth=$dbh->prepare($site_ID_sql);
	$get_dir_sth = $dbh->prepare($get_dir_sql);
	$del_dir_sth = $dbh->prepare(q{update vf_files set file_available=0,file_state=3 WHERE file_parent=?});
	$get_site_from_id_sth = $dbh->prepare(q{select * FROM vf_sites WHERE site_id=?});
	$get_unavailable_file_sth = $dbh->prepare(q{select file_id FROM vf_files WHERE file_available=1 and file_parent IN
		( select file_id FROM vf_files WHERE file_available=0 )});
	$set_unavailable_file_sth = $dbh->prepare(q{UPDATE vf_files set file_available=0 WHERE file_id = ? });
}

#Close database connection.
sub close_db{

	$new_file_sth->finish;
	$up_file_sth->finish;
	$del_file_sth->finish;
	$get_file_sth->finish;
	$site_ID_sth->finish;
	$get_dir_sth->finish;
	$del_dir_sth->finish;
	$get_site_from_id_sth->finish;

	$dbh->disconnect() or die "Disconnect to database failed.\n";
}

sub commit_db{
	$dbh->commit();
}

sub get_site_id{
	my $site = shift;
	$site_ID_sth->execute($site) or die "Error";
	my $site_ID_ref = $site_ID_sth->fetchrow_hashref();
	return $site_ID_ref->{'site_id'};
}

#Get file_id field of a file or directory.
#Argument: $dir ,$site_id
sub get_file_id{
	my ($dir, $site_id) = @_;
	$get_file_sth->execute($dir, $site_id) or die "Error";
	my $ref = $get_file_sth->fetchrow_hashref();
	return $ref->{'file_id'};
}

sub get_site_from_id{
	my @site_array;
	foreach my $site_id (@_){
		$get_site_from_id_sth->execute($site_id) or die "Error";
		push(@site_array, $get_site_from_id_sth->fetchrow_hashref());
	}
	return @site_array;
}

#Operate on table vf_files
sub op_file_db{
	my ($item, $item_type, $item_attr, $size, $date, $time, $is_dir);
	my ($url_n, $url_o, $parent_url, $parent);
	my ($item_type, $item, $site, $site_id, $root_dir) = @_;
	
	if($site eq ""){
		print "Site is unkown.\n";
		return;
	}

	$url_n = $cur_dir_path.$item;
	$url_o =$old_dir_path.$item;
	Encode::from_to($url_n, 'euc-cn', 'utf8');  #set character set to utf8
	Encode::from_to($url_o, 'euc-cn', 'utf8');

	
	if($item_type eq "NewItem"){
		print "Add item "."$item"."\n";
		$item_attr = $new_item_hash{$item};
		($size, $date, $time, $is_dir) = split(/\s+/, $item_attr);
		$parent_url = $cur_dir_path;
		if($cur_dir_path eq $root_dir){
			$parent = undef;
		}else{
			$parent_url =~ s/\/$//g;
			Encode::from_to($parent_url, 'euc-cn', 'utf8');
			$parent = get_file_id($parent_url, $site_id);# or log_fail("NewItem");
		}
		Encode::from_to($item, 'euc-cn', 'utf8');
		#print "$item\t$url_n\t$size\t$dt\t$is_dir\t$site_ID\t$parent";
		$new_file_sth->execute($item, $url_n, $size, "$date $time", $is_dir, $site_id, $parent);
		$filecount ++;
	}elsif($item_type eq "UpItem"){
		#print "Update item "."$item"."\n";
		$item_attr = $new_item_hash{$item};
		($size, $date, $time, $is_dir) = split(/\s+/, $item_attr);	
		Encode::from_to($item, 'euc-cn', 'utf8');
		$up_file_sth->execute( $size, "$date $time", $item, $url_n);
		$dbh->commit;
	}elsif($item_type eq "ItemDel"){
		print "Del item "."$item"."\n";
		Encode::from_to($item, 'euc-cn', 'utf8');
		$del_file_sth->execute($item, $url_o);
		$dbh->commit;
	}elsif($item_type eq "DirDel"){
		$item =~ s/\:$//;
		Encode::from_to($item, 'euc-cn', 'utf8');
		my $parent_id = get_file_id($item, $site_id);
		print "Del Dir $item \n";
		$del_dir_sth->execute($parent_id);
		$dbh->commit;
	}
}


#Get the list of directories to run spider~
sub get_dirs{
	my @site_dirs;
	$get_dir_sth->execute();
	while(my $dir_row_ref = $get_dir_sth->fetchrow_hashref()){
		@$dir_row_ref{'down_site','list_site'} = get_site_from_id(@$dir_row_ref{'down_site_id', 'list_site_id'});
		push(@site_dirs, $dir_row_ref);
	}
	return @site_dirs;
}

#Some files have been deleted on the FTP site
#so set the unavailable field in table vf_files.
sub set_unavailable{
	while(1){
		$get_unavailable_file_sth->execute;
		my $file_ary_ref = $get_unavailable_file_sth->fetchall_arrayref([0]);
		last if( $file_ary_ref);
		#每个file执行一次
		while(my $file_id = shift(@$file_ary_ref)){
			my $unavailable_file_id = $file_id->[0];
			$set_unavailable_file_sth->execute($unavailable_file_id);
		}
		$dbh->commit;
	}
}

1;